package cn.gxg.test.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
 * Access转换为Oracle数据库
 * @author ABBE
 *
 */
public class Access2Oracle {
	public Access2Oracle () {
		
	}
	
	public static void main(String[] args) {
		String accessUrl="jdbc:odbc:MobileODBC"; //此为dsn
//		String accessUrl="jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=Dm_Mobile.mdb";
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			Connection accessConn=DriverManager.getConnection(accessUrl);
			Statement stm=accessConn.createStatement();
			ResultSet rs=stm.executeQuery("select * from Dm_Mobile");
			long startTime = System.currentTimeMillis();
			
			//定义Oracle
			String url = "jdbc:oracle:thin:@172.18.130.113:1521:bidbtest";
			String user = "cq_stat_user";
			String password = "cq_stat_user-1016";
			Connection conn = null;
			try {
				conn = (Connection) DriverManager.getConnection(url,user,password);
				// 关闭事务自动提交
				conn.setAutoCommit(false);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			StringBuffer sql = new StringBuffer();
			sql.append("insert into DM_DATA_MOBILE " +
					"(MOBILENUMBER,MOBILEAREA,MOBILETYPE,MOBILEAREACODE,MOBILEPOSTCODE)" +
					" values (?,?,?,?,?)");
			Class.forName("oracle.jdbc.driver.OracleDriver");
			PreparedStatement pst = (PreparedStatement) conn.prepareStatement(sql.toString());
			
			int i = 0;
			int j = 1; // 记录事务提交次数。
			System.out.println("开始插入数据！");
			while(rs.next()) {
				i++;
				
				pst.setString(1, rs.getString(2));
				pst.setString(2, new String(rs.getBytes(3),"gbk"));
				pst.setString(3, new String(rs.getBytes(4),"gbk"));
				pst.setString(4, rs.getString(5));
				pst.setString(5, rs.getString(6));
				// 把一个SQL命令加入命令列表
				pst.addBatch();
				
				if(i==10) {
					// 执行批量更新
					pst.executeBatch();
					// 语句执行完毕，提交本事务
					conn.commit();
					System.out.println("执行事务，提交第 " + j + " 批数据");
					j++;
					i=0;
				}
			}
			rs.close();
			stm.close();
			accessConn.close();
			
			pst.close();
			conn.close();
			long endTime = System.currentTimeMillis();
			System.out.println("插入数据结束，用时：" + (endTime - startTime));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
